package database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.HashMap;

class DBConnManager {

	// Getting the Connection and connecting to database
	public static Connection getConnection() {
		Connection conn = null;

		try {
			System.out.println("in try block of connection");
			System.out.println(conn);
			conn = null;
			if (conn == null) {

				System.out.println("connection is null");
				Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
				System.out.println("driver loaded");
				conn = DriverManager.getConnection("jdbc:odbc:Login");
				System.out.println("connected with get connection");
			}
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}

	}
}

public class DatabaseCheck {

	// CONSTANTS

	String userConsole = null;
	String myLoginTime = null;
	String passConsole = null;
	String myDesc = null;
	int a, x;
	String passDatabase = null;
	String locationDatabase = null;
	String addressDatabase = null;
	String phoneNoDatabase = null;
	String searchVariable = null;
	public String myUserId = null;
	HashMap<String, String> SearchDetails = new HashMap<String, String>();
	HashMap<String, String> userDetails = new HashMap<String, String>();
	HashMap<String, String> HistDetails = new HashMap<String, String>();
	String database_password, database_userName, database_age, database_gender,
			database_dateOfBirth, database_address;
	String database_state, database_city, database_zipCode, database_emailId,
			database_mobileNo, database_interest;

	// Checking whether USER ID is already created or not
	public int UserExist(String UserID) throws SQLException {
		System.out.println("In user method");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");
			System.out.println(UserID.toString());
			rs = stmt.executeQuery("SELECT userId FROM Login ");
			System.out.println("statement executed");

			while (rs.next()) {
				System.out.println("In rs");
				String check = rs.getString("userId");

				if (check.equals(UserID)) {
					a = 0;
				} else {
					a = 1;
				}

			}

			rs.close();
			stmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();

			}
			if (stmt != null)
				stmt.close();

		}
		return a;
	}

	// Getting the values from the UserInterface Side
	public void UserConsole(String userId, String password) {

		userConsole = userId;
		passConsole = password;
		System.out.println("Got d values from the database");

	}

	// Getting the UserId
	public void myUsrId(String userId) {
		myUserId = userId;
	}

	// Getting the Profile Data
	public HashMap<String, String> ProfileData() throws SQLException {
		System.out.println("you are in the profile Data method");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");
			rs = stmt
					.executeQuery("SELECT * FROM login WHERE userId='U205034' ");
			System.out.println("statement executed");

			while (rs.next()) {
				database_password = rs.getString("password");
				database_city = rs.getString("city");
				database_userName = rs.getString("userName");
				database_state = rs.getString("state");
				database_age = rs.getString("age");
				database_address = rs.getString("address");
				database_gender = rs.getString("gender");
				database_mobileNo = rs.getString("mobileNo");
				database_dateOfBirth = rs.getString("dateOfBirth");
				database_interest = rs.getString("interests");
				database_zipCode = rs.getString("zipCode");
				database_emailId = rs.getString("emailId");

			}

			System.out.println(database_password);
			userDetails.put("map_password", database_password);
			userDetails.put("map_city", database_city);
			userDetails.put("map_age", database_age);
			userDetails.put("map_state", database_state);
			userDetails.put("map_gender", database_gender);
			userDetails.put("map_address", database_address);
			userDetails.put("map_userName", database_userName);
			userDetails.put("map_mobileNo", database_mobileNo);
			userDetails.put("map_zipCode", database_zipCode);
			userDetails.put("map_interest", database_interest);
			userDetails.put("map_dateOfBirth", database_dateOfBirth);
			userDetails.put("map_emailId", database_emailId);
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();

			}
			if (stmt != null)
				stmt.close();

		}
		return userDetails;
	}

	// Getting the time Values for maintaining History
	public void LoginDateValue(String userId, String desc) throws SQLException {
		System.out.println("In LoginDate Value method");
		System.out.println(desc);
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");
			System.out.println(userId + desc);
			stmt.executeUpdate("INSERT INTO hist (iduser,desc)  VALUES ('"
					+ userId + "','" + desc + "') ");

			System.out.println("Data inserted in History Table  successfully");

			stmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();

			}
			if (stmt != null)
				stmt.close();

		}
	}

	// Checking User EXIST or NOT
	public int UserDataBaseValues(String idUser) throws SQLException {
		System.out.println("In userDataBaseValues method");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();
			System.out.println(userConsole);
			stmt = conn.createStatement();
			myUsrId(idUser);
			System.out.println("statement created");
			rs = stmt
					.executeQuery("SELECT userName,password FROM login WHERE userId='"
							+ idUser + "'");
			System.out.println("statement executed");
			while (rs.next()) {
				passDatabase = rs.getString("password");
				System.out.println(passDatabase);
			}

			if (passConsole.equals(passDatabase)) {
				System.out.println("Yuhuuuu");
				x = 1;
			} else {
				System.out.println("shit");
				x = 0;
			}
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();

			}
			if (stmt != null)
				stmt.close();

		}
		return x;
	}

	// Updating the USER PROFILE
	public void UpdateMyProfile(HashMap<String, String> userDetails)
			throws SQLException {
		System.out.println("In StoreUserData method");
		String store_password, store_userName, store_age, store_gender, store_dateOfBirth, store_address;
		String store_state, store_city, store_zipCode, store_emailId, store_mobileNo, store_interest;

		store_address = userDetails.get("map_address");
		store_emailId = userDetails.get("map_emailId");
		store_password = userDetails.get("map_password");
		store_state = userDetails.get("map_state");
		store_mobileNo = userDetails.get("map_mobileNo");
		store_userName = userDetails.get("map_userName");
		store_city = userDetails.get("map_city");
		store_interest = userDetails.get("map_interest");
		store_gender = userDetails.get("map_gender");
		store_zipCode = userDetails.get("map_zipCode");
		store_age = userDetails.get("map_age");
		store_dateOfBirth = userDetails.get("map_dateOfBirth");
		System.out.println("Check pass");
		System.out.println(store_password);
		Connection conn = null;
		Statement stmt = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");

			stmt.executeUpdate("UPDATE  login SET password='" + store_password
					+ "',userName='" + store_userName + "',age='" + store_age
					+ "',gender='" + store_gender + "',dateOfBirth='"
					+ store_dateOfBirth + "',address='" + store_address
					+ "',state='" + store_state + "',city='" + store_city
					+ "',zipCode='" + store_zipCode + "',emailId='"
					+ store_emailId + "',mobileNo='" + store_mobileNo
					+ "',interests='" + store_interest
					+ "'  WHERE userId='U205034'");
			System.out.println("Data inserted successfully");

			stmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();
				// conn=null;

			}
			if (stmt != null)
				stmt.close();

		}
	}

	public void check(String def) throws SQLException {
		System.out.println("In StoreUserData method");
		String store_userId, store_password;

		store_password = def;
		System.out.println(def);

		Connection conn = null;
		Statement stmt = null;
		// ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");

			stmt.executeUpdate("INSERT INTO hist (desc)  VALUES ('"
					+ store_password + "')");
			System.out.println("Data inserted successfully");

			stmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();
				// conn=null;

			}
			if (stmt != null)
				stmt.close();

		}
	}

	// Creating a NEW USER
	public void StoreUserData(HashMap<String, String> userDetails)
			throws SQLException {
		System.out.println("In StoreUserData method");
		String store_userId, store_password, store_userName, store_age, store_gender, store_dateOfBirth, store_address;
		String store_state, store_city, store_zipCode, store_emailId, store_mobileNo, store_interest;

		store_userId = userDetails.get("map_userId");
		store_address = userDetails.get("map_address");
		store_emailId = userDetails.get("map_emailId");
		store_password = userDetails.get("map_password");
		store_state = userDetails.get("map_state");
		store_mobileNo = userDetails.get("map_mobileNo");
		store_userName = userDetails.get("map_userName");
		store_city = userDetails.get("map_city");
		store_interest = userDetails.get("map_interest");
		store_gender = userDetails.get("map_gender");
		store_zipCode = userDetails.get("map_zipCode");
		store_age = userDetails.get("map_age");
		store_dateOfBirth = userDetails.get("map_dateOfBirth");

		Connection conn = null;
		Statement stmt = null;
		// ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");

			stmt.executeUpdate("INSERT INTO login (userId,password,userName,age,gender,dateOfBirth,address,state,city,zipCode,emailId,mobileNo,interests)  VALUES ('"
					+ store_userId
					+ "','"
					+ store_password
					+ "','"
					+ store_userName
					+ "','"
					+ store_age
					+ "','"
					+ store_gender
					+ "','"
					+ store_dateOfBirth
					+ "','"
					+ store_address
					+ "','"
					+ store_state
					+ "','"
					+ store_city
					+ "','"
					+ store_zipCode
					+ "','"
					+ store_emailId
					+ "','"
					+ store_mobileNo
					+ "','"
					+ store_interest + "')");
			System.out.println("Data inserted successfully");

			stmt.close();
			conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {

			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();
				// conn=null;

			}
			if (stmt != null)
				stmt.close();

		}
	}

	// Retrieving the HISTORY Values
	public HashMap<String, String> RetreiveHistoryValues() throws SQLException {

		System.out.println("In Retreive History method");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");
			rs = stmt.executeQuery("SELECT * FROM history ");
			System.out.println("MyRerter" + myUserId);
			System.out.println("statement executed");
			while (rs.next()) {
				myLoginTime = rs.getString("time");
				myDesc = rs.getString("description");
				System.out.println(myLoginTime);

			}
			HistDetails.put("map_Time", myLoginTime);
			HistDetails.put("map_desc", myDesc);
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();

			}
			if (stmt != null)
				stmt.close();

		}
		return HistDetails;

	}

	// Searching the Data for the SUBWAY
	public HashMap<String, String> SearchData(String location, String address,
			String phone) throws SQLException {
		System.out.println("In SearchData method");
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn = DBConnManager.getConnection();

			stmt = conn.createStatement();
			System.out.println("statement created");
			rs = stmt
					.executeQuery("SELECT * FROM subwayOutlet WHERE location='"
							+ location + "' or address='" + address
							+ "' or phoneNo='" + phone + "' ");
			System.out.println("statement executed");
			while (rs.next()) {
				locationDatabase = rs.getString("location");
				addressDatabase = rs.getString("address");
				phoneNoDatabase = rs.getString("phoneNo");
				System.out.println(locationDatabase);
				System.out.println(addressDatabase);
				System.out.println(phoneNoDatabase);

			}
			if (locationDatabase == null || addressDatabase == null
					|| phoneNoDatabase == null) {
				searchVariable = "NotExist";
			} else {
				searchVariable = "Exist";
			}
			System.out.println("FRom Search Database");
			System.out.println(locationDatabase);
			System.out.println(addressDatabase);
			System.out.println(phoneNoDatabase);

			SearchDetails.put("map_location", locationDatabase);
			SearchDetails.put("map_address", addressDatabase);
			SearchDetails.put("map_phoneNo", phoneNoDatabase);
			SearchDetails.put("map_searchVariable", searchVariable);
			rs.close();
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			System.out.println("FINALLY BLOCK");

			if (conn != null) {
				System.out.println("connection is not null");
				conn.close();

			}
			if (stmt != null)
				stmt.close();

		}
		return SearchDetails;
	}
}